package ru.ssau;

import java.util.Collection;
import java.util.LinkedList;
import java.sql.*;
import java.util.logging.Logger;
import lab.domain.*;

/**
 *
 * @author Smike
 */
public class DatabaseHelper {

    private static final String GET_GENRES = "SELECT * FROM Genre";
    private static final String GET_ARTISTS = "SELECT * FROM Artist";
    private static final String GET_TRACKS = "SELECT * FROM AudioTrack";
    private static final String GET_STUDIOS = "SELECT * FROM SoundRecordingStudio";
    private static final String GET_TAGS = "SELECT * FROM ID3v1Tags t JOIN Genre g ON t.Genre_ID = g.ID";
    private static final String GET_GENRE_QUERY = "SELECT * FROM Genre WHERE ID = ?";
    private static final String GET_ARTIST_QUERY = "SELECT * FROM Artist WHERE ID = ?";
    private static final String GET_STUDIO_QUERY = "SELECT * FROM SoundRecordingStudio WHERE ID = ?";
    private static final String GET_ID3V1_TAG_QUERY = "SELECT * FROM ID3v1Tags As t JOIN Genre g ON t.Genre_ID=g.ID WHERE ID3v1GenreID = ?";
    private static final String GET_TRACK_QUERY = "SELECT * FROM AudioTrack WHERE ID = ?";
    private static final String GET_TRACK_GENRES = "SELECT * FROM Track_has_Genre t JOIN Genre g ON t.Genre_ID = g.ID WHERE Track_ID=?";
    private static final String GET_TRACK_ARTISTS = "SELECT * FROM Track_has_Artist t JOIN Artist g ON t.Artist_ID = g.ID WHERE Track_ID=?";
    private static final String INSERT_GENRE = "insert into Genre(ID, Name) values(Genre_ID_SEQ.nextval, ?)";
    private static final String INSERT_ARTIST = "insert into Artist(ID, Name) values(Artist_ID_SEQ.nextval, ?)";
    private static final String INSERT_ID3V1_TAG = "insert into ID3v1Tags(ID3v1GenreID, Genre_ID) values(?, ?)";
    private static final String INSERT_STUDIO = "insert into SoundRecordingStudio(ID, Name) values(SoundRecordingStudio_ID_SEQ.nextval, ?)";
    private static final String INSERT_TRACK = "insert into AudioTrack(ID, Name, Length, Year, SoundRecordingStudio_ID) values(?,?, ?, ?, ?)";
    private static final String INSERT_TRACK_HAS_ARTIST = "insert into Track_Has_Artist(Track_ID, Artist_ID) values (?, ?)";
    private static final String INSERT_TRACK_HAS_GENRE = "insert into Track_Has_Genre(Track_ID, Genre_ID) values (?, ?)";
    private static final String UPDATE_GENRE = "UPDATE Genre SET Name=? WHERE ID = ?";
    private static final String UPDATE_ARTIST = "UPDATE Artist SET Name=? WHERE ID = ?";
    private static final String UPDATE_STUDIO = "UPDATE SoundRecordingStudio SET Name=? WHERE ID = ?";
    private static final String UPDATE_TRACK = "UPDATE AudioTrack SET Name=?, Length=?, Year=?, SoundRecordingStudio_ID=? WHERE ID = ?";
    private static final String DELETE_OLD_TRACKS_ARTISTS = "DELETE FROM Track_has_Artist WHERE Track_ID = ?";
    private static final String DELETE_OLD_TRACKS_GENRES = "DELETE FROM Track_has_Genre WHERE Track_ID = ?";
    private static final String UPDATE_TAG = "UPDATE ID3v1Tags SET Genre_ID=? WHERE ID3v1GenreID = ?";
    private static final String GENRE_SEQUENCE_VALUE = "SELECT Genre_ID_SEQ.nextval FROM dual";
    private static final String STUDIO_SEQUENCE_VALUE = "SELECT SoundRecordingStudio_ID_SEQ.nextval FROM dual";
    private static final String ARTIST_SEQUENCE_VALUE = "SELECT Artist_ID_SEQ.nextval FROM dual";
    private static final String TRACK_SEQUENCE_VALUE = "SELECT Track_ID_SEQ.nextval FROM dual";
    private static final String TAG_SEQUENCE_VALUE = "SELECT ID3v1Tag_ID_SEQ.nextval FROM dual";
    public static final String GENRE_SEQUENCE = "Genre_ID_SEQ";
    public static final String STUDIO_SEQUENCE = "SoundRecordingStudio_ID_SEQ";
    public static final String ARTIST_SEQUENCE = "Artist_ID_SEQ";
    public static final String TRACK_SEQUENCE = "Track_ID_SEQ";
    public static final String TAG_SEQUENCE = "ID3v1Tag_ID_SEQ";
    public static final String SELECT_NEXT_SEQUENCE_VALUE = "SELECT %s.nextval FROM dual";
    public static final String DROP_SEQUENCE = "DROP SEQUENCE %s";
    public static final String CREATE_SEQUENCE = "CREATE SEQUENCE %s START WITH %d INCREMENT BY 1 NOCYCLE";
    public static final String DELETE_GENRE = "DELETE FROM Genre WHERE ID = ?";
    public static final String DELETE_TAG = "DELETE FROM ID3v1Tags WHERE ID3v1GenreID = ?";
    public static final String DELETE_ARTIST = "DELETE FROM Artist WHERE ID = ?";
    public static final String DELETE_STUDIO = "DELETE FROM SoundRecordingStudio WHERE ID = ?";
    public static final String DELETE_TRACK = "DELETE FROM AudioTrack WHERE ID = ?";

    public static Record findById(Connection connection, Record record) {
        return null;
    }

    public static Artist findArtist(Connection connection, int artistId) throws SQLException {
        Artist artist = null;
        PreparedStatement findArtist = connection.prepareStatement(GET_ARTIST_QUERY);
        findArtist.setInt(1, artistId);
        ResultSet cursor = findArtist.executeQuery();
        try {
            if (cursor.next()) {
                artist = new Artist();
                artist.setId(cursor.getInt("ID"));
                artist.setName(cursor.getString("Name"));
            }
        } finally {
            cursor.close();
        }
        return artist;
    }

    public static int insertArtist(Connection connection, Artist artist) throws SQLException {
//        int id = getId(connection, ARTIST_SEQUENCE_VALUE);
//        if (id > 0) {
            PreparedStatement insertArtist = connection.prepareStatement(INSERT_ARTIST);
            insertArtist.setString(1, artist.getName());
            insertArtist.executeUpdate();
            connection.commit();
            //return id;
//        }
        return -1;
    }

    public static void updateArtist(Connection connection, Artist artist) throws SQLException {
        PreparedStatement updateArtist = connection.prepareStatement(UPDATE_ARTIST);
        updateArtist.setString(1, artist.getName());
        updateArtist.setInt(2, artist.getId());
        updateArtist.executeUpdate();
        connection.commit();
    }
    
    public static void deleteArtist(Connection connection, Artist artist) throws SQLException {
        PreparedStatement deleteArtist = connection.prepareStatement(DELETE_ARTIST);
        deleteArtist.setInt(1, artist.getId());
        deleteArtist.executeUpdate();
        connection.commit();
    }

    public static Collection<Artist> findArtists(Connection connection) throws SQLException {
        LinkedList<Artist> artists = new LinkedList<Artist>();
        PreparedStatement findArtists = connection.prepareStatement(GET_ARTISTS);
        ResultSet cursor = findArtists.executeQuery();
        try {
            while (cursor.next()) {
                Artist artist = new Artist();
                artist.setId(cursor.getInt("ID"));
                artist.setName(cursor.getString("Name"));
                artists.add(artist);
            }
        } finally {
            cursor.close();
        }
        return artists;
    }

    public static Genre findGenre(Connection connection, int id) throws SQLException {
        Genre genre = null;
        PreparedStatement findGenre = connection.prepareStatement(GET_GENRE_QUERY);
        findGenre.setInt(1, id);
        ResultSet cursor = findGenre.executeQuery();
        try {
            if (cursor.next()) {
                genre = new Genre();
                genre.setId(cursor.getInt("ID"));
                genre.setName(cursor.getString("Name"));
            }
        } finally {
            cursor.close();
        }
        return genre;
    }

    public static int insertGenre(Connection connection, Genre genre) throws SQLException {
//        int id = getId(connection, GENRE_SEQUENCE_VALUE);
//        Logger.getLogger("WTF!?").severe("id is " + id);
//        Logger.getLogger("WTF!?").severe("genre.id is " + genre.getId());
//        if (id > 0) {
            PreparedStatement insertGenre = connection.prepareStatement(INSERT_GENRE);
            insertGenre.setString(1, genre.getName());
            insertGenre.executeUpdate();
            connection.commit();
//        }
        return -1;
    }

    public static void updateGenre(Connection connection, Genre genre) throws SQLException {
        PreparedStatement updateGenre = connection.prepareStatement(UPDATE_GENRE);
        updateGenre.setString(1, genre.getName());
        updateGenre.setInt(2, genre.getId());
        updateGenre.executeUpdate();
        connection.commit();
    }
    
    public static void deleteGenre(Connection connection, Genre genre) throws SQLException {
        PreparedStatement deleteGenre = connection.prepareStatement(DELETE_GENRE);
        deleteGenre.setInt(1, genre.getId());
        deleteGenre.executeUpdate();
        connection.commit();
    }

    public static Collection<Genre> findGenres(Connection connection) throws SQLException {
        LinkedList<Genre> genres = new LinkedList<Genre>();
        PreparedStatement findGenres = connection.prepareStatement(GET_GENRES);
        ResultSet cursor = findGenres.executeQuery();
        try {
            while (cursor.next()) {
                Genre genre = new Genre();
                genre.setId(cursor.getInt("ID"));
                genre.setName(cursor.getString("Name"));
                genres.add(genre);
            }
        } finally {
            cursor.close();
        }
        return genres;
    }

    public static SoundRecordingStudio findStudio(Connection connection, int id) throws SQLException {
        SoundRecordingStudio studio = null;
        PreparedStatement findStudio = connection.prepareStatement(GET_STUDIO_QUERY);
        findStudio.setInt(1, id);
        ResultSet cursor = findStudio.executeQuery();
        try {
            if (cursor.next()) {
                studio = new SoundRecordingStudio();
                studio.setId(cursor.getInt("ID"));
                studio.setName(cursor.getString("Name"));
            }
        } finally {
            cursor.close();
        }
        return studio;
    }

    public static int insertStudio(Connection connection, SoundRecordingStudio studio) throws SQLException {
//        int id = getId(connection, STUDIO_SEQUENCE_VALUE);
//        if (id > 0) {
            PreparedStatement insertStudio = connection.prepareStatement(INSERT_STUDIO);
            insertStudio.setString(1, studio.getName());
            insertStudio.executeUpdate();
            connection.commit();
//        }
        return -1;
    }

    public static void updateStudio(Connection connection, SoundRecordingStudio studio) throws SQLException {
        PreparedStatement updateStudio = connection.prepareStatement(UPDATE_STUDIO);
        updateStudio.setString(1, studio.getName());
        updateStudio.setInt(2, studio.getId());
        updateStudio.executeUpdate();
        connection.commit();
    }
    
    public static void deleteStudio(Connection connection, SoundRecordingStudio studio) throws SQLException {
        PreparedStatement deleteStudio = connection.prepareStatement(DELETE_STUDIO);
        deleteStudio.setInt(1, studio.getId());
        deleteStudio.executeUpdate();
        connection.commit();
    }

    public static Collection<SoundRecordingStudio> findStudios(Connection connection) throws SQLException {
        LinkedList<SoundRecordingStudio> studios = new LinkedList<SoundRecordingStudio>();
        PreparedStatement findStudios = connection.prepareStatement(GET_STUDIOS);
        ResultSet cursor = findStudios.executeQuery();
        try {
            while (cursor.next()) {
                SoundRecordingStudio studio = new SoundRecordingStudio();
                studio.setId(cursor.getInt("ID"));
                studio.setName(cursor.getString("Name"));
                studios.add(studio);
            }
        } finally {
            cursor.close();
        }
        return studios;
    }

    private static ID3v1Tag findTag(Connection connection, int id) throws SQLException {
        ID3v1Tag tag = null;
        PreparedStatement findTag = connection.prepareStatement(GET_ID3V1_TAG_QUERY);
        findTag.setInt(1, id);
        ResultSet cursor = findTag.executeQuery();
        try {
            if (cursor.next()) {
                tag = new ID3v1Tag();
                tag.setId(cursor.getInt("ID3v1GenreID"));
                Genre genre = new Genre();
                genre.setId(cursor.getInt("Genre_ID"));
                genre.setName(cursor.getString("Name"));
                tag.setGenre(genre);
            }
        } finally {
            cursor.close();
        }
        return tag;
    }

    public static int insertTag(Connection connection, ID3v1Tag tag) throws SQLException {
        int id = getId(connection, TAG_SEQUENCE_VALUE);
        if (id > 0) {
            PreparedStatement insertTag = connection.prepareStatement(INSERT_ID3V1_TAG);
            insertTag.setInt(1, id);
            insertTag.setInt(2, tag.getGenre().getId());
            insertTag.executeUpdate();
            connection.commit();
        }
        return id;
    }

    public static void updateTag(Connection connection, ID3v1Tag tag) throws SQLException {
        PreparedStatement updateTag = connection.prepareStatement(UPDATE_TAG);
        updateTag.setInt(1, tag.getGenre().getId());
        updateTag.setInt(2, tag.getId());
        updateTag.executeUpdate();
        connection.commit();
    }
    
     public static void deleteTag(Connection connection, ID3v1Tag tag) throws SQLException {
        PreparedStatement deleteTag = connection.prepareStatement(DELETE_TAG);
        deleteTag.setInt(1, tag.getId());
        deleteTag.executeUpdate();
        connection.commit();
    }

    public static Collection<ID3v1Tag> findTags(Connection connection) throws SQLException {
        LinkedList<ID3v1Tag> tags = new LinkedList<ID3v1Tag>();
        PreparedStatement findStudios = connection.prepareStatement(GET_TAGS);
        ResultSet cursor = findStudios.executeQuery();
        try {
            while (cursor.next()) {
                ID3v1Tag tag = new ID3v1Tag();
                tag.setId(cursor.getInt("ID3v1GenreID"));
                Genre genre = new Genre();
                genre.setId(cursor.getInt("Genre_ID"));
                genre.setName(cursor.getString("Name"));
                tag.setGenre(genre);
                tags.add(tag);
            }
        } finally {
            cursor.close();
        }
        return tags;
    }

    public static Track findTrack(Connection connection, int id) throws SQLException {
        Track track = null;
        PreparedStatement findTrack = connection.prepareStatement(GET_TRACK_QUERY);
        findTrack.setInt(1, id);
        ResultSet cursor = findTrack.executeQuery();
        try {
            if (cursor.next()) {
                track = new Track();
                track.setId(cursor.getInt("ID"));
                track.setName(cursor.getString("Name"));
                track.setLength(cursor.getLong("Length"));
                track.setYear(cursor.getInt("Year"));
                track.setSoundRecordingStudio(findStudio(connection, cursor.getInt("SoundRecordingStudio_ID")));
                track.setArtists(findTrackArtists(connection, track.getId()));
                track.setGenres(findTrackGenres(connection, track.getId()));
            }
        } finally {
            cursor.close();
        }
        return track;
    }

    public static int insertTrack(Connection connection, Track track) throws SQLException {
//        int id = getId(connection, TRACK_SEQUENCE_VALUE);
//        if (id > 0) {
            track.setId(getId(connection, TRACK_SEQUENCE_VALUE));
            PreparedStatement insertTrack = connection.prepareStatement(INSERT_TRACK);
            insertTrack.setInt(1, track.getId());
            insertTrack.setString(2, track.getName());
            insertTrack.setLong(3, track.getLength());
            insertTrack.setInt(4, track.getYear());
            insertTrack.setInt(5, track.getSoundRecordingStudio().getId());
            insertTrack.executeUpdate();
            connection.commit();
            insertTrackArtists(connection, track);
            insertTrackGenres(connection, track);
//        }
        return -1;
    }

    public static void updateTrack(Connection connection, Track track) throws SQLException {
        PreparedStatement updateTrack = connection.prepareStatement(UPDATE_TRACK);
        updateTrack.setString(1, track.getName());
        updateTrack.setLong(2, track.getLength());
        updateTrack.setInt(3, track.getYear());
        updateTrack.setInt(4, track.getSoundRecordingStudio().getId());
        updateTrack.setInt(5, track.getId());
        updateTrack.executeUpdate();
        connection.commit();
        PreparedStatement deleteOldArtists = connection.prepareStatement(DELETE_OLD_TRACKS_ARTISTS);
        deleteOldArtists.setInt(1, track.getId());
        deleteOldArtists.executeUpdate();

        PreparedStatement deleteOldGenres = connection.prepareStatement(DELETE_OLD_TRACKS_GENRES);
        deleteOldGenres.setInt(1, track.getId());
        deleteOldGenres.executeUpdate();

        insertTrackArtists(connection, track);
        insertTrackGenres(connection, track);
    }
    
    public static void deleteTrack(Connection connection, Track track) throws SQLException {
        PreparedStatement deleteTrack = connection.prepareStatement(DELETE_TRACK);
        deleteTrack.setInt(1, track.getId());
        deleteTrack.executeUpdate();
        connection.commit();
    }

    private static void insertTrackArtists(Connection connection, Track track) throws SQLException {
        PreparedStatement insertArtists = connection.prepareStatement(INSERT_TRACK_HAS_ARTIST);
        for (Artist artist : track.getArtists()) {
            insertArtists.setInt(1, track.getId());
            insertArtists.setInt(2, artist.getId());
            insertArtists.addBatch();
        }
        insertArtists.executeBatch();
        connection.commit();
    }

    private static void insertTrackGenres(Connection connection, Track track) throws SQLException {
        PreparedStatement insertGenres = connection.prepareStatement(INSERT_TRACK_HAS_GENRE);
        for (Genre genre : track.getGenres()) {
            insertGenres.setInt(1, track.getId());
            insertGenres.setInt(2, genre.getId());
            insertGenres.addBatch();
        }
        insertGenres.executeBatch();
        connection.commit();
    }

    public static Collection<Track> findTracks(Connection connection) throws SQLException {
        LinkedList<Track> tracks = new LinkedList<Track>();
        PreparedStatement findTracks = connection.prepareStatement(GET_TRACKS);
        ResultSet cursor = findTracks.executeQuery();
        try {
            while (cursor.next()) {
                Track track = new Track();
                track.setId(cursor.getInt("ID"));
                track.setName(cursor.getString("Name"));
                track.setLength(cursor.getLong("Length"));
                track.setYear(cursor.getInt("Year"));
                track.setSoundRecordingStudio(findStudio(connection, cursor.getInt("SoundRecordingStudio_ID")));
                track.setArtists(findTrackArtists(connection, track.getId()));
                track.setGenres(findTrackGenres(connection, track.getId()));
                tracks.add(track);
            }
        } finally {
            cursor.close();
        }
        return tracks;
    }

    private static ChangableHashSet<Artist> findTrackArtists(Connection connection, int trackId) throws SQLException {
        ChangableHashSet<Artist> result = new ChangableHashSet<Artist>();
        PreparedStatement findArtists = connection.prepareStatement(GET_TRACK_ARTISTS);
        findArtists.setInt(1, trackId);
        ResultSet cursor = findArtists.executeQuery();
        try {
            while (cursor.next()) {
                Artist artist = new Artist();
                artist.setId(cursor.getInt("ID"));
                artist.setName(cursor.getString("Name"));
                result.add(artist);
            }
        } finally {
            cursor.close();
        }
        return result;
    }

    private static ChangableHashSet<Genre> findTrackGenres(Connection connection, int trackId) throws SQLException {
        ChangableHashSet<Genre> result = new ChangableHashSet<Genre>();
        PreparedStatement findArtists = connection.prepareStatement(GET_TRACK_GENRES);
        findArtists.setInt(1, trackId);
        ResultSet cursor = findArtists.executeQuery();
        while (cursor.next()) {
            Genre genre = new Genre();
            genre.setId(cursor.getInt("ID"));
            genre.setName(cursor.getString("Name"));
            result.add(genre);
        }
        return result;
    }
    
    private static int getId(Connection connection, String statement) throws SQLException {
        PreparedStatement getId = connection.prepareStatement(statement);
        ResultSet cursor = getId.executeQuery();
        try {
            if (cursor.next()) {
                return cursor.getInt(1);
            }
        } finally {
            cursor.close();
        }
        return -1;
    }
}
